package com.wish.demo;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.awt.*;
import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;


/**
 * @author tantexian
 * @since 2016/7/1
 */
public class DoExcel {

    private static Workbook workbook;
    private static FileInputStream fileInputStream1;
    private static CellStyle defaultCellStyle;

    // 打开excel
    private static String excelPathFrom = "E:\\考勤记录表.xlsx";

    private static String excelResultPathOut = "E:\\result.xlsx";


    public static void main(String[] args) throws IOException, ParseException {
        main0(excelPathFrom, excelResultPathOut);
    }


    public static void main0(String excelPathFrom, String excelResultPathOut)
            throws IOException, ParseException {
        long hourSum = 0;
        long weekHourSum = 0;

        Sheet sheet = getSheetAtExcel(excelPathFrom, 0);
        defaultCellStyle = sheet.getRow(0).getCell(0).getCellStyle();

        short lastCellNum = sheet.getRow(0).getLastCellNum();
        int addIndexStart = lastCellNum;

        addCell(sheet, 0, addIndexStart, "上班时间");
        addCell(sheet, 0, addIndexStart + 1, "下班时间");
        addCell(sheet, 0, addIndexStart + 2, "加班时间(小时)");
        addCell(sheet, 0, addIndexStart + 3, "星期");
        addCell(sheet, 0, addIndexStart + 4, "打卡状态");

        int lastRowNum = sheet.getPhysicalNumberOfRows();
        System.out.println("lastRowNum ==" + lastRowNum);

        int startRow = 0;
        int endRow = 0;
        boolean isLastStatusOK = false;

        for (int i = 1; i < lastRowNum - 1; i++) {
            startRow = endRow = i;
            Date startDate = getDate(sheet, startRow);
            Date nowDate = startDate;
            Date nextDate = getDate(sheet, startRow + 1);

            while (isSameDate(startDate, nextDate)) {
                nowDate = nextDate;
                endRow++;
                if (endRow + 1 < lastRowNum) {
                    nextDate = getDate(sheet, endRow + 1);
                }
                else {
                    // 进入此处逻辑，表示最后一天打卡正常，及打卡两次以上。否则最后一天只打卡一次
                    isLastStatusOK = true;
                    break;
                }

            }

            i = endRow;

            String statusStr = "正常";

            if (endRow == startRow) {
                addCell(sheet, startRow, addIndexStart, date2String(startDate));
                addCell(sheet, startRow, addIndexStart + 1, "忘打卡");

                statusStr = "打卡一次";
                System.out.println("当前打卡异常 Row ：" + startRow);
            }
            else {
                addCell(sheet, startRow, addIndexStart, date2String(startDate));
                addCell(sheet, startRow, addIndexStart + 1, date2String(nowDate));

                long hours = 0;
                long diff = nowDate.getTime() - startDate.getTime();
                if (isWeekend(nowDate) && !isWeekendButNeedWork(nowDate)) {
                    hours = diff / (1000 * 60 * 60);
                    weekHourSum = weekHourSum + hours;
                    statusStr = "周末加班";
                }
                else {
                    hours = diff / (1000 * 60 * 60) - 9;
                    if (hours < 0) {
                        statusStr = "打卡异常";
                        hours = 0;
                    }
                }

                hourSum = hourSum + hours;

                addCell(sheet, startRow, addIndexStart + 2, hours + "小时");
            }

            String weekStr = getWeekStr(nowDate);

            addCell(sheet, startRow, addIndexStart + 3, weekStr);

            addCell(sheet, startRow, addIndexStart + 4, statusStr);

            // 删除中间多余打卡行记录
            for (int k = startRow + 1; k <= endRow; k++) {
                delRow(sheet, k);
            }

            System.out.println("上班打卡时间（row " + startRow + "） ：" + date2String(startDate) + "    下班打卡时间（row "
                    + endRow + "） ：" + date2String(nowDate));

        }

        // 特殊处理最后一天只打卡一次逻辑
        if (!isLastStatusOK) {
            Cell cell = null;
            Date date = getDate(sheet, lastRowNum - 1);
            cell = sheet.getRow(sheet.getLastRowNum()).createCell(addIndexStart);
            cell.setCellValue(date2String(date));

            cell = sheet.getRow(sheet.getLastRowNum()).createCell(addIndexStart + 1);
            cell.setCellValue("忘打卡");

            String weekStr = getWeekStr(date);
            cell = sheet.getRow(sheet.getLastRowNum()).createCell(addIndexStart + 3);
            cell.setCellValue(weekStr);

            cell = sheet.getRow(sheet.getLastRowNum()).createCell(addIndexStart + 4);
            cell.setCellValue("打卡一次");
        }

        // 上移所有非空白行
        delAllSpaceRows(sheet);

        System.out.println("总加班时间为：" + hourSum + "小时");

        // 添加总加班时间到excel
        Cell cell = sheet.createRow(sheet.getLastRowNum() + 1).createCell(addIndexStart + 4);
        cell.setCellValue("总加班时间为：" + hourSum + "小时(周末:" + weekHourSum + "小时)");
        cell.setCellStyle(defaultCellStyle);

        workbook.write(new FileOutputStream(new File(excelResultPathOut)));

    }


    private static String getWeekStr(Date nowDate) {
        String dayStr = "星期";
        switch (nowDate.getDay()) {
        case 1:
            dayStr = dayStr + "一";
            break;
        case 2:
            dayStr = dayStr + "二";
            break;
        case 3:
            dayStr = dayStr + "三";
            break;
        case 4:
            dayStr = dayStr + "四";
            break;
        case 5:
            dayStr = dayStr + "五";
            break;
        case 6:
            dayStr = dayStr + "六";
            break;
        case 0:
            dayStr = dayStr + "天";
            break;

        }
        return dayStr;
    }


    private static void delAllSpaceRows(Sheet sheet) {
        int lastRow = sheet.getLastRowNum();
        while (lastRow > 0) {
            lastRow--;
            Row row = sheet.getRow(lastRow);
            if (row == null) {
                sheet.shiftRows(lastRow + 1, sheet.getLastRowNum(), -1);
            }

        }
    }


    private static void addCell(Sheet sheet, int row, int col, Date date) {
        Cell cell = sheet.getRow(row).createCell(col);
        cell.setCellValue(date);
    }


    private static void addCell(Sheet sheet, int row, int col, String str) {
        Cell cell = sheet.getRow(row).createCell(col);
        cell.setCellValue(str);
    }


    private static void delRow(Sheet sheet, int row) {
        sheet.removeRow(sheet.getRow(row));
    }


    private static Sheet getSheetAtExcel(String excelPath, int sheetAt) throws IOException {
        Sheet sheet = null;

        fileInputStream1 = new FileInputStream(new File(excelPath));
        workbook = new XSSFWorkbook(fileInputStream1);

        // 获取sheet0
        sheet = workbook.getSheetAt(sheetAt);

        return sheet;
    }


    /**
     * @author tantexian
     * @params row 行
     * @since 2016/7/1
     */
    public static Date getDate(Sheet sheet, int row) throws ParseException {
        String s = null;
        int col = TitleEnum.TIME.getIndex();
        Cell cell = sheet.getRow(row).getCell(col);
        if (col == TitleEnum.TIME.getIndex()) {
            s = cell.toString();
            s = s.substring(0, s.length() - 2);
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = null;
        date = sdf.parse(s);

        return date;
    }


    private static boolean isSameDate(Date date1, Date date2) {
        Calendar cal1 = Calendar.getInstance();
        cal1.setTime(date1);

        Calendar cal2 = Calendar.getInstance();
        cal2.setTime(date2);

        boolean isSameYear = cal1.get(Calendar.YEAR) == cal2.get(Calendar.YEAR);
        boolean isSameMonth = isSameYear && cal1.get(Calendar.MONTH) == cal2.get(Calendar.MONTH);
        boolean isSameDate =
                isSameMonth && cal1.get(Calendar.DAY_OF_MONTH) == cal2.get(Calendar.DAY_OF_MONTH);

        return isSameDate;
    }


    /**
     * dade 转换为String
     *
     * @author tantexian
     * @params
     * @since 2016/7/2
     */
    private static String date2String(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return sdf.format(date);
    }


    /**
     * 判断是否是周末
     *
     * @return
     */
    private static boolean isWeekend(Date date) {
        Calendar cal1 = Calendar.getInstance();
        cal1.setTime(date);
        int week = cal1.get(Calendar.DAY_OF_WEEK) - 1;
        if (week == 6 || week == 0) {// 0代表周日，6代表周六
            return true;
        }
        return false;
    }


    /**
     * 判断是否是国家规定正常上班的周末日期
     *
     * @author tantexian
     * @params
     * @since 2016/7/2
     */
    private static boolean isWeekendButNeedWork(Date date) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        List<String> dateArrayList = new ArrayList<String>();
        // 此处添加当前所有国家规定上班的周末日期
        dateArrayList.add("2016-06-12");// 端午
        dateArrayList.add("2016-09-18");// 中秋
        dateArrayList.add("2016-10-08");// 国庆
        dateArrayList.add("2016-10-09");// 国庆
        for (int i = 0; i < dateArrayList.size(); i++) {
            if (isSameDate(sdf.parse(dateArrayList.get(i)), date)) {
                return true;
            }

        }
        return false;
    }
}
